using System;
using System.Data;
using System.Data.SqlClient;
using Entity;

namespace SalesVMT
{
    public class ReceiptDB
    {
        #region PUBLIC
        public static DataSet GetOne(int _Receipt_ID, string _Receipt_Type)
        {
            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();

            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetInfo", dbConn);
            dbCmd.Parameters.AddWithValue("@Receipt_ID", _Receipt_ID);
            dbCmd.Parameters.AddWithValue("@Receipt_Type", _Receipt_Type);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataSet GetAllByProviderForSpend(int ProviderID, string _Type)
        {
            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();

            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetAllByProviderForSpend", dbConn);
            dbCmd.Parameters.AddWithValue("@ProviderID", ProviderID);
            dbCmd.Parameters.AddWithValue("@Receipt_Type", _Type);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataSet GetList(out int ItemCount, string keyWord, string type, int currentPage, int pageSize)
        {

            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetList", dbConn);

            dbCmd.Parameters.AddWithValue("@KeyWord", keyWord);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@RPP", pageSize);
            dbCmd.Parameters.AddWithValue("@Type", type);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataSet GetListProviderDebit(out int ItemCount,int ProviderID, int currentPage, int pageSize)
        {

            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetListProviderDebit", dbConn);
            dbCmd.Parameters.AddWithValue("@ProviderID", ProviderID);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@RPP", pageSize);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataSet GetProviderDebit(int ProviderID)
        {

            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetProviderDebit", dbConn);
            dbCmd.Parameters.AddWithValue("@ProviderID", ProviderID);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataSet GetOrderPurchase(out int ItemCount, int ProviderID, DateTime FromDate, DateTime ToDate, int currentPage, int pageSize)
        {

            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetOrderPurchase", dbConn);
            dbCmd.Parameters.AddWithValue("@ProviderID", ProviderID);
            dbCmd.Parameters.AddWithValue("@FromDate", FromDate);
            dbCmd.Parameters.AddWithValue("@ToDate", ToDate);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@RPP", pageSize);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataSet GetOrderPurchaseForReport(int ProviderID, DateTime FromDate, DateTime ToDate)
        {

            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetOrderPurchaseForReport", dbConn);
            dbCmd.Parameters.AddWithValue("@ProviderID", ProviderID);
            dbCmd.Parameters.AddWithValue("@FromDate", FromDate);
            dbCmd.Parameters.AddWithValue("@ToDate", ToDate);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool Delete(int _Receipt_ID)
        {
            bool retVal = false;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_Delete", dbConn);
            dbCmd.Parameters.AddWithValue("@Receipt_ID", _Receipt_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static int Insert(ReceiptInfo _ReceiptInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_Insert", dbConn);
            dbCmd.Parameters.AddWithValue("@Receipt_Code", _ReceiptInfo.Receipt_Code);
            dbCmd.Parameters.AddWithValue("@Receipt_Date", _ReceiptInfo.Receipt_Date);
            dbCmd.Parameters.AddWithValue("@Receipt_ProviderID", _ReceiptInfo.Receipt_ProviderID);
            dbCmd.Parameters.AddWithValue("@Receipt_UsersID", _ReceiptInfo.Receipt_UsersID);
            dbCmd.Parameters.AddWithValue("@Receipt_Sender", _ReceiptInfo.Receipt_Sender);
            dbCmd.Parameters.AddWithValue("@Receipt_TotalPrice", _ReceiptInfo.Receipt_TotalPrice);
            dbCmd.Parameters.AddWithValue("@Receipt_TotalDownPrice", _ReceiptInfo.Receipt_TotalDownPrice);
            dbCmd.Parameters.AddWithValue("@Receipt_TotalVAT", _ReceiptInfo.Receipt_TotalVAT);
            dbCmd.Parameters.AddWithValue("@Receipt_Total", _ReceiptInfo.Receipt_Total);
            dbCmd.Parameters.AddWithValue("@Receipt_Remaining", _ReceiptInfo.Receipt_Remaining);
            dbCmd.Parameters.AddWithValue("@Receipt_Debit", _ReceiptInfo.Receipt_Debit);
            dbCmd.Parameters.AddWithValue("@Receipt_Type", _ReceiptInfo.Receipt_Type);
            dbCmd.Parameters.AddWithValue("@Receipt_Status", _ReceiptInfo.Receipt_Status);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static ReceiptInfo GetInfo(int _Receipt_ID, string _Receip_Type)
        {
            ReceiptInfo retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetInfo", dbConn);
            dbCmd.Parameters.AddWithValue("@Receipt_ID", _Receipt_ID);
            dbCmd.Parameters.AddWithValue("@Receipt_Type", _Receip_Type);
            dbCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new ReceiptInfo();
                    retVal.Receipt_ID = Convert.ToInt32(dr["Receipt_ID"]);
                    retVal.Receipt_Code = Convert.ToString(dr["Receipt_Code"]);
                    retVal.Receipt_Date = Convert.ToDateTime(dr["Receipt_Date"]);
                    retVal.Receipt_ProviderID = Convert.ToInt32(dr["Receipt_ProviderID"]);
                    retVal.Receipt_Sender = Convert.ToString(dr["Receipt_Sender"]);
                    retVal.Receipt_Pay = Convert.ToDecimal(dr["Receipt_Pay"]);
                    retVal.Receipt_Remaining = Convert.ToDecimal(dr["Receipt_Remaining"]);
                    if (dr["Receipt_Debit"] != DBNull.Value)
                        retVal.Receipt_Debit = Convert.ToDecimal(dr["Receipt_Debit"]);
                    else
                        retVal.Receipt_Debit = 0;
                    retVal.Receipt_Status = Convert.ToString(dr["Receipt_Status"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        public static string GetMaxCode()
        {
            string retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();

            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_GetMaxCode", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                retVal = Convert.ToString(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static int Using(int ReceiptID)
        {
            int retVal = 0;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Receipt_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Receipt_ID", ReceiptID);

            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
     
        #endregion
    }
}
